﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_HrmMasters
    {
        public D_HrmMasters()
        {

        }
        public DataSet LoadNamePrefixes(Nullable<int> Sequence, Nullable<int> Gender, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@Genders", Gender);

                strSQL = "SELECT np.NAME_PREFIX_SEQ, np.PREFIX_NAME, np.PREFIX_SHORT, np.PREFIX_NAME_ENG"
                            + ", np.PREFIX_SHORT_ENG, np.GENDER_SEQ, g.GENDER_NAME, np.CREATED_BY, np.UPDATED_BY"
                     + " FROM MAS_NAME_PREFIXES np"
                     + " LEFT JOIN MAS_GENDERS g ON np.GENDER_SEQ = g.GENDER_SEQ"
                     + " WHERE np.RECORD_STATUS <> @DeleteRecord"
                     + (Sequence.HasValue ? " AND np.NAME_PREFIX_SEQ = @Sequence " : "")
                     + (Gender.HasValue ? " AND (np.GENDER_SEQ = @Genders OR np.GENDER_SEQ IS NULL )" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;

            }
            catch (Exception ex)
            {
                return null;
                throw new Exception("{class MasNamePrefixDAL}:" + ex.Message);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public DataSet LoadNationalities(Nullable<int> Sequence, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);

                strSQL = " SELECT NAT_SEQ, NAT_NAME, NAT_NAME_ENG, RECORD_STATUS, CREATED_BY, UPDATED_BY"
                    + " FROM MAS_NATIONALITIES"
                    + " WHERE RECORD_STATUS <> @DeleteRecord"
                    + (Sequence.HasValue ? " AND NAT_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadRelegions(Nullable<int> Sequence, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);

                strSQL = "SELECT RELIGION_SEQ, RELIGION_NAME, RELIGION_NAME_ENG, CREATED_BY, UPDATED_BY"
                    + " FROM MAS_RELIGIONS"
                    + " WHERE RECORD_STATUS <> @DeleteRecord"
                    + (Sequence.HasValue ? " AND RELIGION_SEQ = @Sequence" : "")
                    + " ORDER BY RELIGION_NAME";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public DataSet LoadBloodGroups(Nullable<int> Sequence, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);

                strSQL = "SELECT BLOOD_GROUP_SEQ, BLOOD_GROUP_NAME, BLOOD_GROUP_NAME_ENG, REMARKS"
                        + ", RECORD_STATUS, CREATED_BY, UPDATED_BY"
                    + " FROM MAS_BLOOD_GROUPS"
                    + " WHERE RECORD_STATUS <> @DeleteRecord"
                    + (Sequence.HasValue ? " AND BLOOD_GROUP_SEQ = @Sequence" : "")
                    + " ORDER BY BLOOD_GROUP_NAME";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return ds;
        }
        public DataSet LoadEducationDegree(Nullable<int> Sequence, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);

                strSQL = "SELECT EDU_DEGREE_SEQ, EDU_DEGREE_CODE, EDU_DEGREE_NAME, EDU_DEGREE_NAME_ENG"
                        + ", EDU_DEGREE_QUALIFICATION, COMPARATIVE_LEVEL, RECORD_STATUS, CREATED_BY, UPDATED_BY"
                    + " FROM MAS_EDU_DEGREES"
                    + " WHERE RECORD_STATUS <> @DeleteRecord"
                    + (Sequence.HasValue ? " AND EDU_DEGREE_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadEducationInstitutes(Nullable<int> Sequence, Nullable<int> Country, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@Country", Country);
                dbManager.AddParameters(3, "@SearchText", SearchText);

                strSQL = "SELECT ins.EDU_INS_SEQ, ins.EDU_INS_NAME, ins.EDU_INS_ENG, ins.EDU_GOV"
                        + ", ins.MAX_EDU_DEGREE_SEQ, ins.ADDRESS, ins.POSTAL_CODE, ins.TEL, ins.FAX"
                        + ", ins.DISTRICT_SEQ, ins.COUNTRY_SEQ, ins.EDU_INS_CODE"
                        + ", ins.RECORD_STATUS, ins.CREATED_BY, ins.UPDATED_BY"
                        + ", d.DISTRICT_NAME, c.COUNTRY_NAME"
                    + " FROM MAS_EDU_INSTITUTES ins"
                        + " LEFT JOIN MAS_DISTRICTS d ON ins.DISTRICT_SEQ = d.DISTRICT_SEQ"
                        + " LEFT JOIN MAS_COUNTRIES c ON ins.COUNTRY_SEQ = c.COUNTRY_SEQ"
                    + " WHERE ins.RECORD_STATUS = @ActiveRecord"
                    + (Sequence.HasValue ? " AND ins.EDU_INS_SEQ = @Seq" : "")
                    + (Country.HasValue ? Country == 1 ? " AND ins.COUNTRY_SEQ <> @Country" : " AND ins.COUNTRY_SEQ = @Country" : "")
                    + " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                        + " ELSE (ins.EDU_INS_NAME LIKE CONCAT('%', @SearchText , '%')"
                            + " OR c.COUNTRY_NAME LIKE CONCAT('%', @SearchText , '%')"
                        + ") END"
                    + " ORDER BY ins.EDU_INS_CODE";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadEducationMajors(Nullable<int> Sequence, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@SearchText", SearchText);

                strSQL = "SELECT EDU_MAJOR_SEQ, EDU_MAJOR_CODE, EDU_MAJOR_NAME, EDU_MAJOR_NAME_ENG"
                        + ", RECORD_STATUS, CREATED_BY, UPDATED_BY"
                    + " FROM MAS_EDU_MAJORS"
                    + " WHERE RECORD_STATUS <> @DeleteRecord"
                    + (Sequence.HasValue ? " AND EDU_MAJOR_SEQ = @Sequence" : "")
                    + " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                        + " ELSE (EDU_MAJOR_NAME LIKE CONCAT('%', @SearchText , '%')"
                        + ") END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadEducationQualifications(Nullable<int> Sequence, Nullable<int> Degree, string SearchText)
        {
            DataSet ds = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@Degree", Degree);
                dbManager.AddParameters(3, "@SearchText", SearchText);

                strSQL = "SELECT q.EDU_QUAL_SEQ, q.EDU_QUAL_CODE, q.EDU_QUAL_NAME, q.EDU_QUAL_NAME_ENG"
                        + ", q.EDU_QUAL_NAME_SHORT, q.EDU_DEGREE_SEQ, d.EDU_DEGREE_NAME"
                        + ", q.RECORD_STATUS, q.CREATED_BY, q.UPDATED_BY"
                    + " FROM MAS_EDU_QUALIFICATIONS q"
                        + " LEFT JOIN MAS_EDU_DEGREES d ON q.EDU_DEGREE_SEQ = d.EDU_DEGREE_SEQ"
                    + " WHERE q.RECORD_STATUS <> @DeleteRecord"
                    + (Sequence.HasValue ? " AND q.EDU_QUAL_SEQ = @Sequence" : "")
                    + (Degree.HasValue ? " AND q.EDU_DEGREE_SEQ = @Degree" : "")
                    + " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                        + " ELSE (q.EDU_QUAL_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR d.EDU_DEGREE_NAME LIKE CONCAT('%', @SearchText , '%')"
                        + ") END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
    }
}
